﻿#region Using directives

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;

#endregion

namespace ApplicationLibrary.SqlConnector
{
    #region ConvertColumnType

    public static class ConvertColumnType
    {
        public static string TypeAsString(Type typeIn)
        {
            string typeOut = string.Empty;

            if (typeIn == typeof(string) || typeIn == typeof(String))
                typeOut = "nvarchar";
            else
                if (typeIn == typeof(Int32) || typeIn == typeof(int) || typeIn.BaseType == typeof(Enum))
                    typeOut = "int";
                else
                    if (typeIn == typeof(bool) || typeIn == typeof(Boolean))
                        typeOut = "bit";
                    else
                        if (typeIn == typeof(Byte))
                            typeOut = "tinyint";
                        else
                            if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(Double))
                                typeOut = "decimal";
                            else
                                if (typeIn == typeof(DateTime))
                                    typeOut = "datetime";
                                else
                                    Debug.Assert(false, "CreateTable " + typeIn.ToString(), "Tipo colonna sconosciuto");

            return typeOut;
        }

        public static int LenOfType<T>()
        {
            int typeOut = 0;
            Type typeIn = typeof(T);

            if (typeIn == typeof(Int32) || typeIn == typeof(int) || typeIn.BaseType == typeof(Enum))
                typeOut = 4;
            else
                if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(double))
                    typeOut = 9;

            return typeOut;
        }

        public static int DecOf<T>()
        {
            int typeOut = 0;
            Type typeIn = typeof(T);
            if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(double))
                typeOut = 2;

            return typeOut;
        }

        public static string DefaultValue<T>()
        {
            string typeOut = "";
            Type typeIn = typeof(T);
            if (typeIn == typeof(string) || typeIn == typeof(String))
                typeOut = "";
            else
                if (typeIn == typeof(Int32) || typeIn == typeof(int) || typeIn.BaseType == typeof(Enum))
                    typeOut = "0";
                else
                    if (typeIn == typeof(bool) || typeIn == typeof(Boolean))
                        typeOut = "False";
                    else
                        if (typeIn == typeof(Byte))
                            typeOut = "0";
                        else
                            if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(Double))
                                typeOut = "0.0";
                            else
                                if (typeIn == typeof(DateTime))
                                    typeOut = "1799/12/31";
                                else
                                    Debug.Assert(false, "CreateTable " + typeIn.ToString(), "Tipo colonna sconosciuto");

            return typeOut;
        }

        public static SqlDbType SqlTypeOf(Type typeIn)
        {
            SqlDbType typeOut = SqlDbType.VarChar;

            if (typeIn == typeof(string) || typeIn == typeof(String))
                typeOut = SqlDbType.VarChar;
            else
                if (typeIn == typeof(Int32) || typeIn == typeof(int) || typeIn.BaseType == typeof(Enum))
                    typeOut = SqlDbType.Int;
                else
                    if (typeIn == typeof(bool) || typeIn == typeof(Boolean))
                        typeOut = SqlDbType.Bit;
                    else
                        if (typeIn == typeof(Byte))
                            typeOut = SqlDbType.SmallInt;
                        else
                            if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(Double))
                                typeOut = SqlDbType.Decimal;
                            else
                                if (typeIn == typeof(DateTime))
                                    typeOut = SqlDbType.DateTime;
                                else
                                    Debug.Assert(false, "SqlTypeOf " + typeIn.ToString(), "Tipo colonna sconosciuto");

            return typeOut;
        }

        public static SqlDbType SqlTypeCEOf(Type typeIn)
        {
            SqlDbType typeOut = SqlDbType.NVarChar;

            if (typeIn == typeof(string) || typeIn == typeof(String))
                typeOut = SqlDbType.NVarChar;
            else
                if (typeIn == typeof(Int32) || typeIn == typeof(int) || typeIn.BaseType == typeof(Enum))
                    typeOut = SqlDbType.Int;
                else
                    if (typeIn == typeof(bool) || typeIn == typeof(Boolean))
                        typeOut = SqlDbType.Bit;
                    else
                        if (typeIn == typeof(Byte))
                            typeOut = SqlDbType.SmallInt;
                        else
                            if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(Double))
                                typeOut = SqlDbType.Decimal;
                            else
                                if (typeIn == typeof(DateTime))
                                    typeOut = SqlDbType.DateTime;
                                else
                                    Debug.Assert(false, "SqlTypeCEOf " + typeIn.ToString(), "Tipo colonna sconosciuto");

            return typeOut;
        }

        public static DbType SqlTypeLITOf(Type typeIn)
        {
            DbType typeOut = DbType.String;

            if (typeIn == typeof(string) || typeIn == typeof(String))
                typeOut = DbType.String;
            else
                if (typeIn == typeof(Int32) || typeIn == typeof(int) || typeIn.BaseType == typeof(Enum))
                    typeOut = DbType.Int32;
                else
                    if (typeIn == typeof(bool) || typeIn == typeof(Boolean))
                        typeOut = DbType.Boolean;
                    else
                        if (typeIn == typeof(Byte))
                            typeOut = DbType.Byte;
                        else
                            if (typeIn == typeof(Decimal) || typeIn == typeof(float) || typeIn == typeof(Double))
                                typeOut = DbType.Decimal;
                            else
                                if (typeIn == typeof(DateTime))
                                    typeOut = DbType.DateTime;
                                else
                                    Debug.Assert(false, "SqlTypeLITOf " + typeIn.ToString(), "Tipo colonna sconosciuto");

            return typeOut;
        }
    }

    #endregion

    #region Column

    public interface iColumn
    {
        string TableName { get; }

        string Description { get; }

        string Name { get; }

        int Len { get; }

        int Dec { get; }

        string DefaultValue { get; }

        bool EnableNull { get; }

        bool AutoIncrement { get; }

        string Other { get; }

        bool VisibleInRadar { get; }

        Type ColType { get; }

        Type EnumType { get; }

        string QualifyName { get; }
    }

    public class Column<T> : iColumn
    {
        public string TableName { get; private set; }

        public string Description { get; private set; }

        public string Name { get; private set; }

        public System.Type ColType { get { return typeof(T); } }

        public int Len { get; private set; }

        public int Dec { get; private set; }

        public string DefaultValue { get; private set; }

        public bool EnableNull { get; private set; }

        public bool AutoIncrement { get; private set; }

        public string Other { get; private set; }

        public bool VisibleInRadar { get; private set; }

        public System.Type EnumType { get; private set; }

        public string QualifyName { get { return string.Format("[{0}].[{1}]", TableName, Name); } }

        public T GetValue(object val)
        {
            return (T)Convert.ChangeType(val, typeof(T));
        }

        public override string ToString()
        {
            return Name;
        }

        public Column(string table, string description, string name)
            : this(table, description, name, 0, true, false, null)
        { }

        public Column(string table, string description, string name, int len)
            : this(table, description, name, len, true, false, null)
        { }

        public Column(string table, string description, string name, int len, bool enableNull)
            : this(table, description, name, len, enableNull, false, null)
        { }

        public Column(string table, string description, string name, bool enableNull)
            : this(table, description, name, 0, enableNull, false, null)
        { }

        public Column(string table, string description, string name, bool enableNull, bool autoIncrement)
            : this(table, description, name, 0, enableNull, autoIncrement, null)
        { }

        public Column(string table, string description, string name, int len, bool enableNull, bool autoIncrement, string defaultValue)
        {
            TableName = table;
            Description = description;
            Name = name;
            EnumType = typeof(T).BaseType == typeof(Enum) ? typeof(T) : typeof(System.DBNull);
            Len = len == 0
                    ? ConvertColumnType.LenOfType<T>()
                    : len;
            Dec = ConvertColumnType.DecOf<T>();

            EnableNull = enableNull;
            AutoIncrement = autoIncrement;
            DefaultValue = defaultValue == null
                            ? ConvertColumnType.DefaultValue<T>()
                            : "";
        }
    }

    #endregion

    #region Create Table

    public abstract class Table
    {
        public string Tablename = string.Empty;
        public static DateTime EmptyDate = new DateTime(1799, 12, 31);
        private List<iColumn> visibleInRadar = new List<iColumn>();
        private List<iColumn> tableKey = new List<iColumn>();

        public abstract iColumn ForeignKey { get; }

        public override string ToString()
        {
            return Tablename;
        }

        public void VisibleInRadar(iColumn[] col)
        {
            foreach (iColumn cl in col)
                visibleInRadar.Add(cl);
        }

        public int VisibleInRadarCount { get { return visibleInRadar.Count; } }

        public int SegmentKeyCount { get { return tableKey.Count; } }

        public void DefineKey(iColumn[] col)
        {
            foreach (iColumn cl in col)
                tableKey.Add(cl);
        }

        public iColumn GetKeySegment(int index)
        {
            return tableKey[index];
        }

        public iColumn this[int index]
        {
            get
            {
                return (iColumn)visibleInRadar[index];
            }
        }
    }

    public class SqlCreateTable
    {
        private ProviderType ProviderType;// = ProviderType.SQLServer;
        private SqlABConnection myConnection = null;
        private string CreateString = string.Empty;
        private string TableName = string.Empty;
        private bool columnAdded = false;
        private bool altermode = false;
        private bool firstTime = true;

        public SqlCreateTable(string tableName, SqlABConnection connection)
        {
            myConnection = connection;
            this.ProviderType = connection.providerType;
            CreateString = string.Format("CREATE TABLE {0} (", tableName);
            columnAdded = false;
        }

        public SqlCreateTable(SqlABConnection connection)
        {
            this.ProviderType = connection.providerType;
            myConnection = connection;
            columnAdded = false;
        }

        public void NewTable(object tableName)
        {
            CreateString = string.Format("CREATE TABLE {0} (", tableName);
            TableName = tableName.ToString();
            columnAdded = false;
        }

        public void NewTable(string tableName)
        {
            CreateString = string.Format("CREATE TABLE {0} (", tableName);
            TableName = tableName.ToString();
            columnAdded = false;
        }

        public void DropTable(string tableName)
        {
            CreateString = string.Format("DROP TABLE {0}", tableName);
            TableName = tableName.ToString();
            ExecuteCommand();
        }

        public void AlterTable(string tableName)
        {
            CreateString = string.Format("ALTER TABLE {0}", tableName);
            TableName = tableName.ToString();
            altermode = true;
            firstTime = true;
        }

        public void DropColumn(iColumn columnName)
        {
            Debug.Assert(altermode, "DropColumn ", "Manca il nome della tabella");
            string constraint = GetConstraint(columnName);
            CreateString += (firstTime) ? " DROP " : ", ";

            if (!constraint.Equals(string.Empty))
                CreateString += " CONSTRAINT " + constraint + ",";

            CreateString += " COLUMN " + columnName.Name;
            firstTime = false;
        }

        private string GetConstraint(iColumn columnName)
        {
#if(SQLCompact)
            if (ProviderType == SqlConnector.ProviderType.SQLCompact)
                return "";
#endif

            string command = "select object_name(cdefault) from syscolumns where [id] = object_id(@tn) and [name] like @cn ";
            SqlABCommand cmd = new SqlABCommand(command, myConnection);
            SqlABParameter tn = new SqlABParameter("@tn", typeof(string), 64);
            SqlABParameter cn = new SqlABParameter("@cn", typeof(string), 64);
            cmd.Parameters.Add(tn);
            cmd.Parameters.Add(cn);

            //cmd.Parameters.AddRange(new SqlABParameter[] { tn, cn });

            tn.Value = "dbo." + columnName.TableName;
            cn.Value = columnName.Name;

            string constraint = string.Empty;
            try
            {
                object result = cmd.ExecuteScalar();
                if (result != null)
                    constraint = result.ToString();
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
            }

            return constraint;
        }

        public void AlterColumn(iColumn column)
        {
            string initial = CreateString;

            string constraint = GetConstraint(column);
#if(SQLServer)

            //CreateString += ProviderType == SqlConnector.ProviderType.SQLServer
            //                    ? (constraint != ""
            //                            ? " DROP CONSTRAINT " + constraint
            //                            : "")
            //                    : string.Format(" ALTER COLUMN {0} DROP DEFAULT", column.Name);
#else
            CreateString += string.Format(" ALTER COLUMN {0} DROP DEFAULT", column.Name);
#endif

            firstTime = false;
            altermode = true;
            CreateString += " ALTER COLUMN ";

            AddColumn(column);

            //CreateString += initial + string.Format(" ADD DEFAULT '{0}'", column.DefaultValue)
            //    + " FOR " + column.Name;
        }

        public void AddColumn(iColumn column)
        {
            AddColumn(column.Name, column.ColType, column.Len, column.Dec, column.EnableNull, column.AutoIncrement, column.DefaultValue, column.Other);
        }

        public void AddColumn(string columnName, System.Type datatype, int len, int dec, bool EnableNull, bool autoIncrement, string defaultValue, string other)
        {
            string vartype = string.Empty;
            string initial = "; " + CreateString;

            vartype = ConvertColumnType.TypeAsString(datatype);

            if (altermode)
            {
                if (firstTime) CreateString += " ADD";
                firstTime = false;
            }
            else
                CreateString += (CreateString.EndsWith("(")) ? "" : ", ";

            if (vartype == "int" || vartype == "bit" || vartype == "tinyint" || vartype == "datetime" || vartype == "text")
                CreateString += string.Format(" [{0}] {1}", columnName, vartype);
            else
                if (vartype == "decimal")
                    CreateString += string.Format(" [{0}] {1}({2},{3})", columnName, vartype, len, dec);
                else
                    CreateString += string.Format(" [{0}] {1}({2})", columnName, vartype, len);
            if (!altermode)
                CreateString += (autoIncrement) ? " IDENTITY (1, 1)" : string.Format(" DEFAULT '{0}' {1}", defaultValue, other);
            CreateString += (EnableNull || altermode) ? " NULL" : " NOT NULL";

            //CreateString += (autoIncrement) ? " auto_increment" : string.Format(" DEFAULT '{0}' {1}", defaultValue, other);
            if (altermode)
                CreateString += string.Format("; ALTER TABLE [{0}] ADD DEFAULT '{1}' FOR [{2}]", TableName, defaultValue, columnName);
            columnAdded = true;
        }

        public void AddColumn(string columnName, System.Type datatype, int len, string defaultValue)
        {
            AddColumn(columnName, datatype, len, 0, true, false, defaultValue, "");
        }

        public void AddColumn(string columnName, System.Type datatype, int len)
        {
            AddColumn(columnName, datatype, len, 0, true, false, "", "");
        }

        public void AddPrimaryKey(params string[] keys)
        {
            Debug.Assert(columnAdded, "CreateTable ", "Aggiunta chiave prima di colonna");

            for (int i = 0; i < keys.Length; i++)
                CreateString += (i == 0) ?
                    string.Format(", PRIMARY KEY ([{0}]", keys[i]) :
                    string.Format(",[{0}]", keys[i]);

            CreateString += ")";
        }

        public void AddPrimaryKey(params iColumn[] keys)
        {
            Debug.Assert(columnAdded, "CreateTable ", "Aggiunta chiave prima di colonna");

            for (int i = 0; i < keys.Length; i++)
                CreateString += (i == 0) ?
                    string.Format(", PRIMARY KEY ([{0}]", keys[i].Name) :
                    string.Format(",[{0}]", keys[i].Name);

            CreateString += ")";
        }

        public void AddSecondaryKey(string keyname, params iColumn[] keys)
        {
            Debug.Assert(columnAdded, "CreateTable ", "Aggiunta chiave prima di colonna");

            for (int i = 0; i < keys.Length; i++)
                CreateString += (i == 0) ?
                    string.Format(", KEY {0}({1}", keyname, keys[i].Name) :
                    string.Format(",{0}", keys[i].Name);

            CreateString += ")";
        }

        public bool Create()
        {
            Debug.Assert(columnAdded, "CreateTable ", "Creazione di tabella prima di definizione");
            CreateString += ")";

            columnAdded = false;
            return ExecuteCommand();
        }

        public bool Alter()
        {
            altermode = false;
            return ExecuteCommand();
        }

        private bool ExecuteCommand()
        {
            try
            {
                using (SqlABCommand MyCommand = new SqlABCommand(CreateString, myConnection))
                {
                    MyCommand.ExecuteNonQuery();
                }
                return true;
            }
            catch (System.Exception ex)
            {
                Debug.Assert(false, ex.ToString(), CreateString);
                return false;
            }
        }
    }

    #endregion
}